{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# 工具包"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "#coding=utf-8\n",
    "import pandas as pd\n",
    "import numpy as np\n",
    "import time\n",
    "\n",
    "#Python SQLITE数据库是一款非常小巧的嵌入式开源数据库软件\n",
    "import sqlite3"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# 载入原始数据\n",
    "\n",
    "### Get more information about the Millionsong project from https://labrosa.ee.columbia.edu/millionsong/\n",
    "\n",
    "## Load Triplets data  [user, song, play_count]\n",
    "\n",
    "#### Get the data from http://labrosa.ee.columbia.edu/millionsong/sites/default/files/challenge/train_triplets.txt.zip"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2017-09-24T09:48:06.127761Z",
     "start_time": "2017-09-24T09:48:05.969929Z"
    }
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>user</th>\n",
       "      <th>song</th>\n",
       "      <th>play_count</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>b80344d063b5ccb3212f76538f3d9e43d87dca9e</td>\n",
       "      <td>SOAKIMP12A8C130995</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>b80344d063b5ccb3212f76538f3d9e43d87dca9e</td>\n",
       "      <td>SOAPDEY12A81C210A9</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>b80344d063b5ccb3212f76538f3d9e43d87dca9e</td>\n",
       "      <td>SOBBMDR12A8C13253B</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>b80344d063b5ccb3212f76538f3d9e43d87dca9e</td>\n",
       "      <td>SOBFNSP12AF72A0E22</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>b80344d063b5ccb3212f76538f3d9e43d87dca9e</td>\n",
       "      <td>SOBFOVM12A58A7D494</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>b80344d063b5ccb3212f76538f3d9e43d87dca9e</td>\n",
       "      <td>SOBNZDC12A6D4FC103</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>b80344d063b5ccb3212f76538f3d9e43d87dca9e</td>\n",
       "      <td>SOBSUJE12A6D4F8CF5</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>b80344d063b5ccb3212f76538f3d9e43d87dca9e</td>\n",
       "      <td>SOBVFZR12A6D4F8AE3</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>b80344d063b5ccb3212f76538f3d9e43d87dca9e</td>\n",
       "      <td>SOBXALG12A8C13C108</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>b80344d063b5ccb3212f76538f3d9e43d87dca9e</td>\n",
       "      <td>SOBXHDL12A81C204C0</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                       user                song  play_count\n",
       "0  b80344d063b5ccb3212f76538f3d9e43d87dca9e  SOAKIMP12A8C130995           1\n",
       "1  b80344d063b5ccb3212f76538f3d9e43d87dca9e  SOAPDEY12A81C210A9           1\n",
       "2  b80344d063b5ccb3212f76538f3d9e43d87dca9e  SOBBMDR12A8C13253B           2\n",
       "3  b80344d063b5ccb3212f76538f3d9e43d87dca9e  SOBFNSP12AF72A0E22           1\n",
       "4  b80344d063b5ccb3212f76538f3d9e43d87dca9e  SOBFOVM12A58A7D494           1\n",
       "5  b80344d063b5ccb3212f76538f3d9e43d87dca9e  SOBNZDC12A6D4FC103           1\n",
       "6  b80344d063b5ccb3212f76538f3d9e43d87dca9e  SOBSUJE12A6D4F8CF5           2\n",
       "7  b80344d063b5ccb3212f76538f3d9e43d87dca9e  SOBVFZR12A6D4F8AE3           1\n",
       "8  b80344d063b5ccb3212f76538f3d9e43d87dca9e  SOBXALG12A8C13C108           1\n",
       "9  b80344d063b5ccb3212f76538f3d9e43d87dca9e  SOBXHDL12A81C204C0           1"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#数据集太大，读10000条记录看看\n",
    "triplet_dataset = pd.read_csv(filepath_or_buffer = 'train_triplets.txt', \n",
    "                              nrows=10000,sep='\\t', header=None, \n",
    "                              names=['user','song','play_count'])\n",
    "triplet_dataset.head(n=10)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 计算每个用户的总播放次数\n",
    "看哪些用户最活跃（play counts最多）\n",
    "由于空间有限，不活跃的用户不考虑了（共有约1M用户）"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2017-09-24T10:00:40.238849Z",
     "start_time": "2017-09-24T09:57:54.875213Z"
    }
   },
   "outputs": [],
   "source": [
    "output_dict = {} #user及对应的play_count次数集合(在所有的歌曲上求和)\n",
    "with open('train_triplets.txt') as f:\n",
    "    for line_number, line in enumerate(f):\n",
    "        user = line.split('\\t')[0]            #第一列为用户id\n",
    "        play_count = int(line.split('\\t')[2]) #第三列为播放次数\n",
    "        if user in output_dict:\n",
    "            play_count +=output_dict[user]\n",
    "            output_dict.update({user:play_count})\n",
    "        output_dict.update({user:play_count})\n",
    "output_list = [{'user':k,'play_count':v} for k,v in output_dict.items()]\n",
    "play_count_df = pd.DataFrame(output_list)\n",
    "\n",
    "#按总播放次数排序\n",
    "play_count_df = play_count_df.sort_values(by = 'play_count', ascending = False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [],
   "source": [
    "play_count_df.to_csv(path_or_buf='./output/user_playcount_df.csv', index = False)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 计算每首歌曲的总播放次数\n",
    "看哪些歌曲被播放次数最多（play counts最多）\n",
    "由于空间有限，不受欢迎的歌曲先不考虑（共有约1M用户）\n",
    "\n",
    "其实两个统计次数一起做亦可（如果机器能存储两个字典）"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2017-09-24T10:08:50.652416Z",
     "start_time": "2017-09-24T10:05:53.721519Z"
    }
   },
   "outputs": [],
   "source": [
    "output_dict = {}\n",
    "with open('train_triplets.txt') as f:\n",
    "    for line_number, line in enumerate(f):\n",
    "        song = line.split('\\t')[1]            #第二列为歌曲id\n",
    "        play_count = int(line.split('\\t')[2]) #第三列为播放次数\n",
    "        if song in output_dict:\n",
    "            play_count +=output_dict[song]\n",
    "            output_dict.update({song:play_count})\n",
    "        output_dict.update({song:play_count})\n",
    "output_list = [{'song':k,'play_count':v} for k,v in output_dict.items()]\n",
    "song_count_df = pd.DataFrame(output_list)\n",
    "\n",
    "#按总播放次数排序\n",
    "song_count_df = song_count_df.sort_values(by = 'play_count', ascending = False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [],
   "source": [
    "song_count_df.to_csv(path_or_buf='./output/song_playcount_df.csv', index = False)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 最活跃的用户和最流行的歌曲"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2017-09-24T10:08:54.987662Z",
     "start_time": "2017-09-24T10:08:53.518248Z"
    }
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>play_count</th>\n",
       "      <th>user</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>13132</td>\n",
       "      <td>093cb74eb3c517c5179ae24caf0ebec51b24d2a2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>9884</td>\n",
       "      <td>119b7c88d58d0c6eb051365c103da5caf817bea6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>8210</td>\n",
       "      <td>3fa44653315697f42410a30cb766a4eb102080bb</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>7015</td>\n",
       "      <td>a2679496cd0af9779a92a13ff7c6af5c81ea8c7b</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>6494</td>\n",
       "      <td>d7d2d888ae04d16e994d6964214a1de81392ee04</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>6472</td>\n",
       "      <td>4ae01afa8f2430ea0704d502bc7b57fb52164882</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>6150</td>\n",
       "      <td>b7c24f770be6b802805ac0e2106624a517643c17</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>5656</td>\n",
       "      <td>113255a012b2affeab62607563d03fbdf31b08e7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>5620</td>\n",
       "      <td>6d625c6557df84b60d90426c0116138b617b9449</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>5602</td>\n",
       "      <td>99ac3d883681e21ea68071019dba828ce76fe94d</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   play_count                                      user\n",
       "0       13132  093cb74eb3c517c5179ae24caf0ebec51b24d2a2\n",
       "1        9884  119b7c88d58d0c6eb051365c103da5caf817bea6\n",
       "2        8210  3fa44653315697f42410a30cb766a4eb102080bb\n",
       "3        7015  a2679496cd0af9779a92a13ff7c6af5c81ea8c7b\n",
       "4        6494  d7d2d888ae04d16e994d6964214a1de81392ee04\n",
       "5        6472  4ae01afa8f2430ea0704d502bc7b57fb52164882\n",
       "6        6150  b7c24f770be6b802805ac0e2106624a517643c17\n",
       "7        5656  113255a012b2affeab62607563d03fbdf31b08e7\n",
       "8        5620  6d625c6557df84b60d90426c0116138b617b9449\n",
       "9        5602  99ac3d883681e21ea68071019dba828ce76fe94d"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "play_count_df = pd.read_csv(filepath_or_buffer='./output/user_playcount_df.csv')\n",
    "play_count_df.head(n =10)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2017-09-24T10:09:06.800300Z",
     "start_time": "2017-09-24T10:09:06.427869Z"
    }
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>play_count</th>\n",
       "      <th>song</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>726885</td>\n",
       "      <td>SOBONKR12A58A7A7E0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>648239</td>\n",
       "      <td>SOAUWYT12A81C206F1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>527893</td>\n",
       "      <td>SOSXLTC12AF72A7F54</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>425463</td>\n",
       "      <td>SOFRQTD12A81C233C0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>389880</td>\n",
       "      <td>SOEGIYH12A6D4FC0E3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>356533</td>\n",
       "      <td>SOAXGDH12A8C13F8A1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>292642</td>\n",
       "      <td>SONYKOW12AB01849C9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>274627</td>\n",
       "      <td>SOPUCYA12A8C13A694</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>268353</td>\n",
       "      <td>SOUFTBI12AB0183F65</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>244730</td>\n",
       "      <td>SOVDSJC12A58A7A271</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   play_count                song\n",
       "0      726885  SOBONKR12A58A7A7E0\n",
       "1      648239  SOAUWYT12A81C206F1\n",
       "2      527893  SOSXLTC12AF72A7F54\n",
       "3      425463  SOFRQTD12A81C233C0\n",
       "4      389880  SOEGIYH12A6D4FC0E3\n",
       "5      356533  SOAXGDH12A8C13F8A1\n",
       "6      292642  SONYKOW12AB01849C9\n",
       "7      274627  SOPUCYA12A8C13A694\n",
       "8      268353  SOUFTBI12AB0183F65\n",
       "9      244730  SOVDSJC12A58A7A271"
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "song_count_df = pd.read_csv(filepath_or_buffer='./output/song_playcount_df.csv')\n",
    "song_count_df.head(10)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 从总体数据中抽取子集\n",
    "前10万个用户\n",
    "前10万个（10%）用户的播放次数和占总播放次数的40%（2/8原则：20%的用户播放了80%的次数）"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "(1019318, 2)\n",
      "(384546, 2)\n"
     ]
    }
   ],
   "source": [
    "print(play_count_df.shape)\n",
    "print(song_count_df.shape)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2017-09-24T10:16:05.763243Z",
     "start_time": "2017-09-24T10:16:05.700186Z"
    }
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "40.8807280500655"
      ]
     },
     "execution_count": 25,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "total_play_count = sum(song_count_df.play_count)\n",
    "(float(play_count_df.head(n=100000).play_count.sum())/total_play_count)*100"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2017-09-24T10:26:37.537061Z",
     "start_time": "2017-09-24T10:26:37.528055Z"
    }
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "78.39315366645269"
      ]
     },
     "execution_count": 26,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#取3万首歌曲（10%）\n",
    "#前3万首歌曲的播放次数占总播放次数的80%\n",
    "(float(song_count_df.head(n=30000).play_count.sum())/total_play_count)*100"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2017-09-24T10:26:38.311410Z",
     "start_time": "2017-09-24T10:26:38.306426Z"
    }
   },
   "outputs": [],
   "source": [
    "play_count_subset = play_count_df.head(n=100000)\n",
    "song_count_subset = song_count_df.head(n=30000)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2017-09-24T10:26:39.337554Z",
     "start_time": "2017-09-24T10:26:39.326529Z"
    }
   },
   "outputs": [],
   "source": [
    "user_subset = list(play_count_subset.user)\n",
    "song_subset = list(song_count_subset.song)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2017-09-24T10:30:23.563673Z",
     "start_time": "2017-09-24T10:27:22.711377Z"
    }
   },
   "outputs": [],
   "source": [
    "triplet_dataset = pd.read_csv(filepath_or_buffer = 'train_triplets.txt',sep='\\t', header=None, names=['user','song','play_count'])\n",
    "triplet_dataset_sub = triplet_dataset[triplet_dataset.user.isin(user_subset)]\n",
    "triplet_dataset_sub_song = triplet_dataset_sub[triplet_dataset_sub.song.isin(song_subset)]\n",
    "del(triplet_dataset)\n",
    "del(triplet_dataset_sub)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(10775315, 3)"
      ]
     },
     "execution_count": 31,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "triplet_dataset_sub_song.to_csv(path_or_buf ='./output/triplet_dataset_sub_song.csv', index=False)\n",
    "triplet_dataset_sub_song.shape"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "即使这样，还有1千万条记录"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2017-09-24T10:33:25.958644Z",
     "start_time": "2017-09-24T10:33:25.939631Z"
    }
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>user</th>\n",
       "      <th>song</th>\n",
       "      <th>play_count</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>498</th>\n",
       "      <td>d6589314c0a9bcbca4fee0c93b14bc402363afea</td>\n",
       "      <td>SOADQPP12A67020C82</td>\n",
       "      <td>12</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>499</th>\n",
       "      <td>d6589314c0a9bcbca4fee0c93b14bc402363afea</td>\n",
       "      <td>SOAFTRR12AF72A8D4D</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>500</th>\n",
       "      <td>d6589314c0a9bcbca4fee0c93b14bc402363afea</td>\n",
       "      <td>SOANQFY12AB0183239</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>501</th>\n",
       "      <td>d6589314c0a9bcbca4fee0c93b14bc402363afea</td>\n",
       "      <td>SOAYATB12A6701FD50</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>502</th>\n",
       "      <td>d6589314c0a9bcbca4fee0c93b14bc402363afea</td>\n",
       "      <td>SOBOAFP12A8C131F36</td>\n",
       "      <td>7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>503</th>\n",
       "      <td>d6589314c0a9bcbca4fee0c93b14bc402363afea</td>\n",
       "      <td>SOBONKR12A58A7A7E0</td>\n",
       "      <td>26</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>504</th>\n",
       "      <td>d6589314c0a9bcbca4fee0c93b14bc402363afea</td>\n",
       "      <td>SOBZZDU12A6310D8A3</td>\n",
       "      <td>7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>505</th>\n",
       "      <td>d6589314c0a9bcbca4fee0c93b14bc402363afea</td>\n",
       "      <td>SOCAHRT12A8C13A1A4</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>506</th>\n",
       "      <td>d6589314c0a9bcbca4fee0c93b14bc402363afea</td>\n",
       "      <td>SODASIJ12A6D4F5D89</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>507</th>\n",
       "      <td>d6589314c0a9bcbca4fee0c93b14bc402363afea</td>\n",
       "      <td>SODEAWL12AB0187032</td>\n",
       "      <td>8</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                         user                song  play_count\n",
       "498  d6589314c0a9bcbca4fee0c93b14bc402363afea  SOADQPP12A67020C82          12\n",
       "499  d6589314c0a9bcbca4fee0c93b14bc402363afea  SOAFTRR12AF72A8D4D           1\n",
       "500  d6589314c0a9bcbca4fee0c93b14bc402363afea  SOANQFY12AB0183239           1\n",
       "501  d6589314c0a9bcbca4fee0c93b14bc402363afea  SOAYATB12A6701FD50           1\n",
       "502  d6589314c0a9bcbca4fee0c93b14bc402363afea  SOBOAFP12A8C131F36           7\n",
       "503  d6589314c0a9bcbca4fee0c93b14bc402363afea  SOBONKR12A58A7A7E0          26\n",
       "504  d6589314c0a9bcbca4fee0c93b14bc402363afea  SOBZZDU12A6310D8A3           7\n",
       "505  d6589314c0a9bcbca4fee0c93b14bc402363afea  SOCAHRT12A8C13A1A4           5\n",
       "506  d6589314c0a9bcbca4fee0c93b14bc402363afea  SODASIJ12A6D4F5D89           1\n",
       "507  d6589314c0a9bcbca4fee0c93b14bc402363afea  SODEAWL12AB0187032           8"
      ]
     },
     "execution_count": 25,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "triplet_dataset_sub_song.head(n=10)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 将歌曲元数据与用户播放记录合并\n",
    "\n",
    "#### 歌曲元数据\n",
    "Get the data from http://labrosa.ee.columbia.edu/millionsong/sites/default/files/AdditionalFiles/track_metadata.db"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2017-09-24T13:48:12.545636Z",
     "start_time": "2017-09-24T13:48:12.536631Z"
    }
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[('songs',)]"
      ]
     },
     "execution_count": 32,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "conn = sqlite3.connect('track_metadata.db')\n",
    "cur = conn.cursor()\n",
    "cur.execute(\"SELECT name FROM sqlite_master WHERE type='table'\")\n",
    "cur.fetchall()  #数据表"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2017-09-24T13:59:00.464363Z",
     "start_time": "2017-09-24T13:58:47.127394Z"
    }
   },
   "outputs": [],
   "source": [
    "track_metadata_df = pd.read_sql(con=conn, sql='select * from songs')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "metadata": {
    "scrolled": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "(30447, 14)\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>track_id</th>\n",
       "      <th>title</th>\n",
       "      <th>song_id</th>\n",
       "      <th>release</th>\n",
       "      <th>artist_id</th>\n",
       "      <th>artist_mbid</th>\n",
       "      <th>artist_name</th>\n",
       "      <th>duration</th>\n",
       "      <th>artist_familiarity</th>\n",
       "      <th>artist_hotttnesss</th>\n",
       "      <th>year</th>\n",
       "      <th>track_7digitalid</th>\n",
       "      <th>shs_perf</th>\n",
       "      <th>shs_work</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>115</th>\n",
       "      <td>TRMMGCB128E079651D</td>\n",
       "      <td>Get Along (Feat: Pace Won) (Instrumental)</td>\n",
       "      <td>SOHNWIM12A67ADF7D9</td>\n",
       "      <td>Charango</td>\n",
       "      <td>ARU3C671187FB3F71B</td>\n",
       "      <td>067102ea-9519-4622-9077-57ca4164cfbb</td>\n",
       "      <td>Morcheeba</td>\n",
       "      <td>227.47383</td>\n",
       "      <td>0.819087</td>\n",
       "      <td>0.533117</td>\n",
       "      <td>2002</td>\n",
       "      <td>185967</td>\n",
       "      <td>-1</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>123</th>\n",
       "      <td>TRMMGTX128F92FB4D9</td>\n",
       "      <td>Viejo</td>\n",
       "      <td>SOECFIW12A8C144546</td>\n",
       "      <td>Caraluna</td>\n",
       "      <td>ARPAAPH1187FB3601B</td>\n",
       "      <td>f69d655c-ffd6-4bee-8c2a-3086b2be2fc6</td>\n",
       "      <td>Bacilos</td>\n",
       "      <td>307.51302</td>\n",
       "      <td>0.595554</td>\n",
       "      <td>0.400705</td>\n",
       "      <td>0</td>\n",
       "      <td>6825058</td>\n",
       "      <td>-1</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>145</th>\n",
       "      <td>TRMMGDP128F933E59A</td>\n",
       "      <td>I Say A Little Prayer</td>\n",
       "      <td>SOGWEOB12AB018A4D0</td>\n",
       "      <td>The Legendary Hi Records Albums_ Volume 3: Ful...</td>\n",
       "      <td>ARNNRN31187B9AE7B7</td>\n",
       "      <td>fb7272ba-f130-4f0a-934d-6eeea4c18c9a</td>\n",
       "      <td>Al Green</td>\n",
       "      <td>133.58975</td>\n",
       "      <td>0.779490</td>\n",
       "      <td>0.599210</td>\n",
       "      <td>1978</td>\n",
       "      <td>5211723</td>\n",
       "      <td>-1</td>\n",
       "      <td>11898</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>172</th>\n",
       "      <td>TRMMHBF12903CF6E59</td>\n",
       "      <td>At the Ball_ That's All</td>\n",
       "      <td>SOJGCRL12A8C144187</td>\n",
       "      <td>Best of Laurel &amp; Hardy - The Lonesome Pine</td>\n",
       "      <td>AR1FEUF1187B9AF3E3</td>\n",
       "      <td>4a8ae4fd-ad6f-4912-851f-093f12ee3572</td>\n",
       "      <td>Laurel &amp; Hardy</td>\n",
       "      <td>123.71546</td>\n",
       "      <td>0.438709</td>\n",
       "      <td>0.307120</td>\n",
       "      <td>0</td>\n",
       "      <td>8645877</td>\n",
       "      <td>-1</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>191</th>\n",
       "      <td>TRMMHKG12903CDB1B5</td>\n",
       "      <td>Black Gold</td>\n",
       "      <td>SOHNFBA12AB018CD1D</td>\n",
       "      <td>Total Life Forever</td>\n",
       "      <td>ARVXV1J1187FB5BF88</td>\n",
       "      <td>6a65d878-fcd0-42cf-aff9-ca1d636a8bcc</td>\n",
       "      <td>Foals</td>\n",
       "      <td>386.32444</td>\n",
       "      <td>0.842578</td>\n",
       "      <td>0.514523</td>\n",
       "      <td>2010</td>\n",
       "      <td>9007438</td>\n",
       "      <td>-1</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "               track_id                                      title  \\\n",
       "115  TRMMGCB128E079651D  Get Along (Feat: Pace Won) (Instrumental)   \n",
       "123  TRMMGTX128F92FB4D9                                      Viejo   \n",
       "145  TRMMGDP128F933E59A                      I Say A Little Prayer   \n",
       "172  TRMMHBF12903CF6E59                    At the Ball_ That's All   \n",
       "191  TRMMHKG12903CDB1B5                                 Black Gold   \n",
       "\n",
       "                song_id                                            release  \\\n",
       "115  SOHNWIM12A67ADF7D9                                           Charango   \n",
       "123  SOECFIW12A8C144546                                           Caraluna   \n",
       "145  SOGWEOB12AB018A4D0  The Legendary Hi Records Albums_ Volume 3: Ful...   \n",
       "172  SOJGCRL12A8C144187         Best of Laurel & Hardy - The Lonesome Pine   \n",
       "191  SOHNFBA12AB018CD1D                                 Total Life Forever   \n",
       "\n",
       "              artist_id                           artist_mbid     artist_name  \\\n",
       "115  ARU3C671187FB3F71B  067102ea-9519-4622-9077-57ca4164cfbb       Morcheeba   \n",
       "123  ARPAAPH1187FB3601B  f69d655c-ffd6-4bee-8c2a-3086b2be2fc6         Bacilos   \n",
       "145  ARNNRN31187B9AE7B7  fb7272ba-f130-4f0a-934d-6eeea4c18c9a        Al Green   \n",
       "172  AR1FEUF1187B9AF3E3  4a8ae4fd-ad6f-4912-851f-093f12ee3572  Laurel & Hardy   \n",
       "191  ARVXV1J1187FB5BF88  6a65d878-fcd0-42cf-aff9-ca1d636a8bcc           Foals   \n",
       "\n",
       "      duration  artist_familiarity  artist_hotttnesss  year  track_7digitalid  \\\n",
       "115  227.47383            0.819087           0.533117  2002            185967   \n",
       "123  307.51302            0.595554           0.400705     0           6825058   \n",
       "145  133.58975            0.779490           0.599210  1978           5211723   \n",
       "172  123.71546            0.438709           0.307120     0           8645877   \n",
       "191  386.32444            0.842578           0.514523  2010           9007438   \n",
       "\n",
       "     shs_perf  shs_work  \n",
       "115        -1         0  \n",
       "123        -1         0  \n",
       "145        -1     11898  \n",
       "172        -1         0  \n",
       "191        -1         0  "
      ]
     },
     "execution_count": 34,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#只保留流行歌曲（3万首）的元数据\n",
    "track_metadata_df_sub = track_metadata_df[track_metadata_df.song_id.isin(song_subset)]\n",
    "print(track_metadata_df_sub.shape)\n",
    "track_metadata_df_sub.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "metadata": {},
   "outputs": [],
   "source": [
    "# -*- coding:utf-8 -*-\n",
    "import sys\n",
    "import importlib\n",
    "importlib.reload(sys)\n",
    "\n",
    "track_metadata_df_sub.to_csv(path_or_buf ='./output/track_metadata_df_sub.csv', index=False)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Load up the saved data subsets"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 38,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2017-09-26T05:35:13.855787Z",
     "start_time": "2017-09-26T05:35:00.267829Z"
    }
   },
   "outputs": [],
   "source": [
    "triplet_dataset_sub_song = pd.read_csv(filepath_or_buffer = './output/triplet_dataset_sub_song.csv')\n",
    "track_metadata_df_sub = pd.read_csv(filepath_or_buffer = './output/track_metadata_df_sub.csv')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 39,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>track_id</th>\n",
       "      <th>title</th>\n",
       "      <th>song_id</th>\n",
       "      <th>release</th>\n",
       "      <th>artist_id</th>\n",
       "      <th>artist_mbid</th>\n",
       "      <th>artist_name</th>\n",
       "      <th>duration</th>\n",
       "      <th>artist_familiarity</th>\n",
       "      <th>artist_hotttnesss</th>\n",
       "      <th>year</th>\n",
       "      <th>track_7digitalid</th>\n",
       "      <th>shs_perf</th>\n",
       "      <th>shs_work</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>TRMMGCB128E079651D</td>\n",
       "      <td>Get Along (Feat: Pace Won) (Instrumental)</td>\n",
       "      <td>SOHNWIM12A67ADF7D9</td>\n",
       "      <td>Charango</td>\n",
       "      <td>ARU3C671187FB3F71B</td>\n",
       "      <td>067102ea-9519-4622-9077-57ca4164cfbb</td>\n",
       "      <td>Morcheeba</td>\n",
       "      <td>227.47383</td>\n",
       "      <td>0.819087</td>\n",
       "      <td>0.533117</td>\n",
       "      <td>2002</td>\n",
       "      <td>185967</td>\n",
       "      <td>-1</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>TRMMGTX128F92FB4D9</td>\n",
       "      <td>Viejo</td>\n",
       "      <td>SOECFIW12A8C144546</td>\n",
       "      <td>Caraluna</td>\n",
       "      <td>ARPAAPH1187FB3601B</td>\n",
       "      <td>f69d655c-ffd6-4bee-8c2a-3086b2be2fc6</td>\n",
       "      <td>Bacilos</td>\n",
       "      <td>307.51302</td>\n",
       "      <td>0.595554</td>\n",
       "      <td>0.400705</td>\n",
       "      <td>0</td>\n",
       "      <td>6825058</td>\n",
       "      <td>-1</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>TRMMGDP128F933E59A</td>\n",
       "      <td>I Say A Little Prayer</td>\n",
       "      <td>SOGWEOB12AB018A4D0</td>\n",
       "      <td>The Legendary Hi Records Albums_ Volume 3: Ful...</td>\n",
       "      <td>ARNNRN31187B9AE7B7</td>\n",
       "      <td>fb7272ba-f130-4f0a-934d-6eeea4c18c9a</td>\n",
       "      <td>Al Green</td>\n",
       "      <td>133.58975</td>\n",
       "      <td>0.779490</td>\n",
       "      <td>0.599210</td>\n",
       "      <td>1978</td>\n",
       "      <td>5211723</td>\n",
       "      <td>-1</td>\n",
       "      <td>11898</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>TRMMHBF12903CF6E59</td>\n",
       "      <td>At the Ball_ That's All</td>\n",
       "      <td>SOJGCRL12A8C144187</td>\n",
       "      <td>Best of Laurel &amp; Hardy - The Lonesome Pine</td>\n",
       "      <td>AR1FEUF1187B9AF3E3</td>\n",
       "      <td>4a8ae4fd-ad6f-4912-851f-093f12ee3572</td>\n",
       "      <td>Laurel &amp; Hardy</td>\n",
       "      <td>123.71546</td>\n",
       "      <td>0.438709</td>\n",
       "      <td>0.307120</td>\n",
       "      <td>0</td>\n",
       "      <td>8645877</td>\n",
       "      <td>-1</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>TRMMHKG12903CDB1B5</td>\n",
       "      <td>Black Gold</td>\n",
       "      <td>SOHNFBA12AB018CD1D</td>\n",
       "      <td>Total Life Forever</td>\n",
       "      <td>ARVXV1J1187FB5BF88</td>\n",
       "      <td>6a65d878-fcd0-42cf-aff9-ca1d636a8bcc</td>\n",
       "      <td>Foals</td>\n",
       "      <td>386.32444</td>\n",
       "      <td>0.842578</td>\n",
       "      <td>0.514523</td>\n",
       "      <td>2010</td>\n",
       "      <td>9007438</td>\n",
       "      <td>-1</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "             track_id                                      title  \\\n",
       "0  TRMMGCB128E079651D  Get Along (Feat: Pace Won) (Instrumental)   \n",
       "1  TRMMGTX128F92FB4D9                                      Viejo   \n",
       "2  TRMMGDP128F933E59A                      I Say A Little Prayer   \n",
       "3  TRMMHBF12903CF6E59                    At the Ball_ That's All   \n",
       "4  TRMMHKG12903CDB1B5                                 Black Gold   \n",
       "\n",
       "              song_id                                            release  \\\n",
       "0  SOHNWIM12A67ADF7D9                                           Charango   \n",
       "1  SOECFIW12A8C144546                                           Caraluna   \n",
       "2  SOGWEOB12AB018A4D0  The Legendary Hi Records Albums_ Volume 3: Ful...   \n",
       "3  SOJGCRL12A8C144187         Best of Laurel & Hardy - The Lonesome Pine   \n",
       "4  SOHNFBA12AB018CD1D                                 Total Life Forever   \n",
       "\n",
       "            artist_id                           artist_mbid     artist_name  \\\n",
       "0  ARU3C671187FB3F71B  067102ea-9519-4622-9077-57ca4164cfbb       Morcheeba   \n",
       "1  ARPAAPH1187FB3601B  f69d655c-ffd6-4bee-8c2a-3086b2be2fc6         Bacilos   \n",
       "2  ARNNRN31187B9AE7B7  fb7272ba-f130-4f0a-934d-6eeea4c18c9a        Al Green   \n",
       "3  AR1FEUF1187B9AF3E3  4a8ae4fd-ad6f-4912-851f-093f12ee3572  Laurel & Hardy   \n",
       "4  ARVXV1J1187FB5BF88  6a65d878-fcd0-42cf-aff9-ca1d636a8bcc           Foals   \n",
       "\n",
       "    duration  artist_familiarity  artist_hotttnesss  year  track_7digitalid  \\\n",
       "0  227.47383            0.819087           0.533117  2002            185967   \n",
       "1  307.51302            0.595554           0.400705     0           6825058   \n",
       "2  133.58975            0.779490           0.599210  1978           5211723   \n",
       "3  123.71546            0.438709           0.307120     0           8645877   \n",
       "4  386.32444            0.842578           0.514523  2010           9007438   \n",
       "\n",
       "   shs_perf  shs_work  \n",
       "0        -1         0  \n",
       "1        -1         0  \n",
       "2        -1     11898  \n",
       "3        -1         0  \n",
       "4        -1         0  "
      ]
     },
     "execution_count": 39,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "track_metadata_df_sub.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# 数据清洗"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 40,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2017-09-26T05:35:26.643464Z",
     "start_time": "2017-09-26T05:35:14.956760Z"
    }
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>user</th>\n",
       "      <th>song</th>\n",
       "      <th>listen_count</th>\n",
       "      <th>title</th>\n",
       "      <th>song_id</th>\n",
       "      <th>release</th>\n",
       "      <th>artist_id</th>\n",
       "      <th>artist_name</th>\n",
       "      <th>duration</th>\n",
       "      <th>artist_familiarity</th>\n",
       "      <th>artist_hotttnesss</th>\n",
       "      <th>year</th>\n",
       "      <th>track_7digitalid</th>\n",
       "      <th>shs_perf</th>\n",
       "      <th>shs_work</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>d6589314c0a9bcbca4fee0c93b14bc402363afea</td>\n",
       "      <td>SOADQPP12A67020C82</td>\n",
       "      <td>12</td>\n",
       "      <td>You And Me Jesus</td>\n",
       "      <td>SOADQPP12A67020C82</td>\n",
       "      <td>Tribute To Jake Hess</td>\n",
       "      <td>ARDFZE61187FB45994</td>\n",
       "      <td>Jake Hess</td>\n",
       "      <td>199.26159</td>\n",
       "      <td>0.374553</td>\n",
       "      <td>0.327544</td>\n",
       "      <td>2004</td>\n",
       "      <td>142275</td>\n",
       "      <td>-1</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>d6589314c0a9bcbca4fee0c93b14bc402363afea</td>\n",
       "      <td>SOAFTRR12AF72A8D4D</td>\n",
       "      <td>1</td>\n",
       "      <td>Harder Better Faster Stronger</td>\n",
       "      <td>SOAFTRR12AF72A8D4D</td>\n",
       "      <td>Discovery</td>\n",
       "      <td>ARF8HTQ1187B9AE693</td>\n",
       "      <td>Daft Punk</td>\n",
       "      <td>223.60771</td>\n",
       "      <td>0.902841</td>\n",
       "      <td>1.021256</td>\n",
       "      <td>2007</td>\n",
       "      <td>485119</td>\n",
       "      <td>-1</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>d6589314c0a9bcbca4fee0c93b14bc402363afea</td>\n",
       "      <td>SOANQFY12AB0183239</td>\n",
       "      <td>1</td>\n",
       "      <td>Uprising</td>\n",
       "      <td>SOANQFY12AB0183239</td>\n",
       "      <td>Uprising</td>\n",
       "      <td>ARR3ONV1187B9A2F59</td>\n",
       "      <td>Muse</td>\n",
       "      <td>304.84853</td>\n",
       "      <td>0.929030</td>\n",
       "      <td>0.750427</td>\n",
       "      <td>0</td>\n",
       "      <td>6024257</td>\n",
       "      <td>-1</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>d6589314c0a9bcbca4fee0c93b14bc402363afea</td>\n",
       "      <td>SOAYATB12A6701FD50</td>\n",
       "      <td>1</td>\n",
       "      <td>Breakfast At Tiffany's</td>\n",
       "      <td>SOAYATB12A6701FD50</td>\n",
       "      <td>Home</td>\n",
       "      <td>AR97V5X1187FB4AE98</td>\n",
       "      <td>Deep Blue Something</td>\n",
       "      <td>257.22730</td>\n",
       "      <td>0.655889</td>\n",
       "      <td>0.409729</td>\n",
       "      <td>1993</td>\n",
       "      <td>163047</td>\n",
       "      <td>-1</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>d6589314c0a9bcbca4fee0c93b14bc402363afea</td>\n",
       "      <td>SOBOAFP12A8C131F36</td>\n",
       "      <td>7</td>\n",
       "      <td>Lucky (Album Version)</td>\n",
       "      <td>SOBOAFP12A8C131F36</td>\n",
       "      <td>We Sing.  We Dance.  We Steal Things.</td>\n",
       "      <td>ARML3X41187FB35F2E</td>\n",
       "      <td>Jason Mraz &amp; Colbie Caillat</td>\n",
       "      <td>189.62240</td>\n",
       "      <td>0.842972</td>\n",
       "      <td>0.770117</td>\n",
       "      <td>0</td>\n",
       "      <td>2638597</td>\n",
       "      <td>-1</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                       user                song  listen_count  \\\n",
       "0  d6589314c0a9bcbca4fee0c93b14bc402363afea  SOADQPP12A67020C82            12   \n",
       "1  d6589314c0a9bcbca4fee0c93b14bc402363afea  SOAFTRR12AF72A8D4D             1   \n",
       "2  d6589314c0a9bcbca4fee0c93b14bc402363afea  SOANQFY12AB0183239             1   \n",
       "3  d6589314c0a9bcbca4fee0c93b14bc402363afea  SOAYATB12A6701FD50             1   \n",
       "4  d6589314c0a9bcbca4fee0c93b14bc402363afea  SOBOAFP12A8C131F36             7   \n",
       "\n",
       "                           title             song_id  \\\n",
       "0               You And Me Jesus  SOADQPP12A67020C82   \n",
       "1  Harder Better Faster Stronger  SOAFTRR12AF72A8D4D   \n",
       "2                       Uprising  SOANQFY12AB0183239   \n",
       "3         Breakfast At Tiffany's  SOAYATB12A6701FD50   \n",
       "4          Lucky (Album Version)  SOBOAFP12A8C131F36   \n",
       "\n",
       "                                 release           artist_id  \\\n",
       "0                   Tribute To Jake Hess  ARDFZE61187FB45994   \n",
       "1                              Discovery  ARF8HTQ1187B9AE693   \n",
       "2                               Uprising  ARR3ONV1187B9A2F59   \n",
       "3                                   Home  AR97V5X1187FB4AE98   \n",
       "4  We Sing.  We Dance.  We Steal Things.  ARML3X41187FB35F2E   \n",
       "\n",
       "                   artist_name   duration  artist_familiarity  \\\n",
       "0                    Jake Hess  199.26159            0.374553   \n",
       "1                    Daft Punk  223.60771            0.902841   \n",
       "2                         Muse  304.84853            0.929030   \n",
       "3          Deep Blue Something  257.22730            0.655889   \n",
       "4  Jason Mraz & Colbie Caillat  189.62240            0.842972   \n",
       "\n",
       "   artist_hotttnesss  year  track_7digitalid  shs_perf  shs_work  \n",
       "0           0.327544  2004            142275        -1         0  \n",
       "1           1.021256  2007            485119        -1         0  \n",
       "2           0.750427     0           6024257        -1         0  \n",
       "3           0.409729  1993            163047        -1         0  \n",
       "4           0.770117     0           2638597        -1         0  "
      ]
     },
     "execution_count": 40,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "del(track_metadata_df_sub['track_id'])\n",
    "del(track_metadata_df_sub['artist_mbid'])\n",
    "\n",
    "#去除重复记录\n",
    "track_metadata_df_sub = track_metadata_df_sub.drop_duplicates(['song_id'])\n",
    "\n",
    "#播放历史和歌曲元数据合并\n",
    "triplet_dataset_sub_song_merged = pd.merge(triplet_dataset_sub_song, track_metadata_df_sub, how='left', left_on='song', right_on='song_id')\n",
    "triplet_dataset_sub_song_merged.rename(columns={'play_count':'listen_count'},inplace=True)\n",
    "\n",
    "triplet_dataset_sub_song_merged.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 41,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2017-09-26T05:35:31.415206Z",
     "start_time": "2017-09-26T05:35:29.271749Z"
    }
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>user</th>\n",
       "      <th>song</th>\n",
       "      <th>listen_count</th>\n",
       "      <th>title</th>\n",
       "      <th>release</th>\n",
       "      <th>artist_name</th>\n",
       "      <th>year</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>d6589314c0a9bcbca4fee0c93b14bc402363afea</td>\n",
       "      <td>SOADQPP12A67020C82</td>\n",
       "      <td>12</td>\n",
       "      <td>You And Me Jesus</td>\n",
       "      <td>Tribute To Jake Hess</td>\n",
       "      <td>Jake Hess</td>\n",
       "      <td>2004</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>d6589314c0a9bcbca4fee0c93b14bc402363afea</td>\n",
       "      <td>SOAFTRR12AF72A8D4D</td>\n",
       "      <td>1</td>\n",
       "      <td>Harder Better Faster Stronger</td>\n",
       "      <td>Discovery</td>\n",
       "      <td>Daft Punk</td>\n",
       "      <td>2007</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>d6589314c0a9bcbca4fee0c93b14bc402363afea</td>\n",
       "      <td>SOANQFY12AB0183239</td>\n",
       "      <td>1</td>\n",
       "      <td>Uprising</td>\n",
       "      <td>Uprising</td>\n",
       "      <td>Muse</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>d6589314c0a9bcbca4fee0c93b14bc402363afea</td>\n",
       "      <td>SOAYATB12A6701FD50</td>\n",
       "      <td>1</td>\n",
       "      <td>Breakfast At Tiffany's</td>\n",
       "      <td>Home</td>\n",
       "      <td>Deep Blue Something</td>\n",
       "      <td>1993</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>d6589314c0a9bcbca4fee0c93b14bc402363afea</td>\n",
       "      <td>SOBOAFP12A8C131F36</td>\n",
       "      <td>7</td>\n",
       "      <td>Lucky (Album Version)</td>\n",
       "      <td>We Sing.  We Dance.  We Steal Things.</td>\n",
       "      <td>Jason Mraz &amp; Colbie Caillat</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>d6589314c0a9bcbca4fee0c93b14bc402363afea</td>\n",
       "      <td>SOBONKR12A58A7A7E0</td>\n",
       "      <td>26</td>\n",
       "      <td>You're The One</td>\n",
       "      <td>If There Was A Way</td>\n",
       "      <td>Dwight Yoakam</td>\n",
       "      <td>1990</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>d6589314c0a9bcbca4fee0c93b14bc402363afea</td>\n",
       "      <td>SOBZZDU12A6310D8A3</td>\n",
       "      <td>7</td>\n",
       "      <td>Don't Dream It's Over</td>\n",
       "      <td>Recurring Dream_ Best Of Crowded House (Domest...</td>\n",
       "      <td>Crowded House</td>\n",
       "      <td>1986</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>d6589314c0a9bcbca4fee0c93b14bc402363afea</td>\n",
       "      <td>SOCAHRT12A8C13A1A4</td>\n",
       "      <td>5</td>\n",
       "      <td>S.O.S.</td>\n",
       "      <td>SOS</td>\n",
       "      <td>Jonas Brothers</td>\n",
       "      <td>2007</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>d6589314c0a9bcbca4fee0c93b14bc402363afea</td>\n",
       "      <td>SODASIJ12A6D4F5D89</td>\n",
       "      <td>1</td>\n",
       "      <td>The Invisible Man</td>\n",
       "      <td>The Invisible Man</td>\n",
       "      <td>Michael Cretu</td>\n",
       "      <td>1985</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>d6589314c0a9bcbca4fee0c93b14bc402363afea</td>\n",
       "      <td>SODEAWL12AB0187032</td>\n",
       "      <td>8</td>\n",
       "      <td>American Idiot [feat. Green Day &amp; The Cast Of ...</td>\n",
       "      <td>The Original Broadway Cast Recording 'American...</td>\n",
       "      <td>Green Day</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                       user                song  listen_count  \\\n",
       "0  d6589314c0a9bcbca4fee0c93b14bc402363afea  SOADQPP12A67020C82            12   \n",
       "1  d6589314c0a9bcbca4fee0c93b14bc402363afea  SOAFTRR12AF72A8D4D             1   \n",
       "2  d6589314c0a9bcbca4fee0c93b14bc402363afea  SOANQFY12AB0183239             1   \n",
       "3  d6589314c0a9bcbca4fee0c93b14bc402363afea  SOAYATB12A6701FD50             1   \n",
       "4  d6589314c0a9bcbca4fee0c93b14bc402363afea  SOBOAFP12A8C131F36             7   \n",
       "5  d6589314c0a9bcbca4fee0c93b14bc402363afea  SOBONKR12A58A7A7E0            26   \n",
       "6  d6589314c0a9bcbca4fee0c93b14bc402363afea  SOBZZDU12A6310D8A3             7   \n",
       "7  d6589314c0a9bcbca4fee0c93b14bc402363afea  SOCAHRT12A8C13A1A4             5   \n",
       "8  d6589314c0a9bcbca4fee0c93b14bc402363afea  SODASIJ12A6D4F5D89             1   \n",
       "9  d6589314c0a9bcbca4fee0c93b14bc402363afea  SODEAWL12AB0187032             8   \n",
       "\n",
       "                                               title  \\\n",
       "0                                   You And Me Jesus   \n",
       "1                      Harder Better Faster Stronger   \n",
       "2                                           Uprising   \n",
       "3                             Breakfast At Tiffany's   \n",
       "4                              Lucky (Album Version)   \n",
       "5                                     You're The One   \n",
       "6                              Don't Dream It's Over   \n",
       "7                                             S.O.S.   \n",
       "8                                  The Invisible Man   \n",
       "9  American Idiot [feat. Green Day & The Cast Of ...   \n",
       "\n",
       "                                             release  \\\n",
       "0                               Tribute To Jake Hess   \n",
       "1                                          Discovery   \n",
       "2                                           Uprising   \n",
       "3                                               Home   \n",
       "4              We Sing.  We Dance.  We Steal Things.   \n",
       "5                                 If There Was A Way   \n",
       "6  Recurring Dream_ Best Of Crowded House (Domest...   \n",
       "7                                                SOS   \n",
       "8                                  The Invisible Man   \n",
       "9  The Original Broadway Cast Recording 'American...   \n",
       "\n",
       "                   artist_name  year  \n",
       "0                    Jake Hess  2004  \n",
       "1                    Daft Punk  2007  \n",
       "2                         Muse     0  \n",
       "3          Deep Blue Something  1993  \n",
       "4  Jason Mraz & Colbie Caillat     0  \n",
       "5                Dwight Yoakam  1990  \n",
       "6                Crowded House  1986  \n",
       "7               Jonas Brothers  2007  \n",
       "8                Michael Cretu  1985  \n",
       "9                    Green Day     0  "
      ]
     },
     "execution_count": 41,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#去掉一些不用的列\n",
    "del(triplet_dataset_sub_song_merged['song_id'])\n",
    "del(triplet_dataset_sub_song_merged['artist_id'])\n",
    "del(triplet_dataset_sub_song_merged['duration'])\n",
    "del(triplet_dataset_sub_song_merged['artist_familiarity'])\n",
    "del(triplet_dataset_sub_song_merged['artist_hotttnesss'])\n",
    "del(triplet_dataset_sub_song_merged['track_7digitalid'])\n",
    "del(triplet_dataset_sub_song_merged['shs_perf'])\n",
    "del(triplet_dataset_sub_song_merged['shs_work'])\n",
    "\n",
    "triplet_dataset_sub_song_merged.head(n=10)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 42,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 推荐系统使用的数据集\n",
    "triplet_dataset_sub_song_merged.to_csv(path_or_buf ='./output/triplet_dataset_sub_song_merged.csv', index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2017-09-24T14:01:44.587532Z",
     "start_time": "2017-09-24T14:01:44.561014Z"
    }
   },
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "anaconda-cloud": {},
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.5.5"
  },
  "toc": {
   "nav_menu": {
    "height": "153px",
    "width": "160px"
   },
   "number_sections": true,
   "sideBar": true,
   "skip_h1_title": false,
   "toc_cell": true,
   "toc_position": {
    "height": "691px",
    "left": "0px",
    "right": "1405px",
    "top": "106px",
    "width": "303px"
   },
   "toc_section_display": "block",
   "toc_window_display": false
  }
 },
 "nbformat": 4,
 "nbformat_minor": 1
}
